package org.lq.dlz.dao.impl;

import java.sql.SQLException;
import java.util.List;

import javax.mail.search.FromStringTerm;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.dlz.common.Page;
import org.lq.dlz.dao.CategoryDao;
import org.lq.dlz.entity.Category;
import org.lq.dlz.entity.Product;
import org.lq.dlz.entity.dto.CategoryDto;
import org.lq.dlz.util.DateUtils;
import org.lq.dlz.util.JDBCUtils;

import com.sun.mail.mbox.NewlineOutputStream;

public class CategoryDaoImpl extends JDBCUtils implements CategoryDao {

	@Override
	public int add(Category cg) {
		String sql = "insert into kb_category(parent_id,name,status,shownav,create_time,update_time) values(?,?,?,?,?,?) ";
		try {
			return queryRunner.update(sql,cg.getParent_id(),cg.getName(),cg.getStatus(),cg.getShownav(),DateUtils.getTimestamp(),DateUtils.getTimestamp());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public int delete(int id) {
		String sql = "delete from kb_category where id=?";

		try {
			queryRunner.update(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	@Override
	public int update(Category cg) {
		String sql = "update  kb_category set parent_id=?,name=?,status=?,shownav=?,create_time=?,update_time=? where id=? ";
		try {
			return queryRunner.update(sql,cg.getParent_id(),cg.getName(),cg.getStatus(),cg.getShownav(),DateUtils.getTimestamp(),DateUtils.getTimestamp(),cg.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return 0;
	}

	@Override
	public List<Category> findPage(Page<Category> page) {
		String sql="select * from kb_category limit ?,?";
		List<Category> list=null;
		try {
			list=queryRunner.query(sql, new BeanListHandler<Category>(Category.class),page.getStartIndex(),page.getPageSize());
			page.setList(list);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Category findById(int id) {
		String sql="select * from  kb_category where id=?";
		Category cg=null;
		 try {
			cg =queryRunner.query(sql, new BeanHandler<Category>(Category.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		 return cg;
	
	}

	@Override
	public List<Category> findByParentId(int parentid, Page<Category> page) {
String sql = "select * from kb_category where parent_id = ? limit ?,? ";
		
		List<Category> list = null;
		
		try {
			list = queryRunner.query(sql, new BeanListHandler<Category>(Category.class),parentid,page.getStartIndex(),page.getPageSize());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	
	@Override
	public List<Category> findByParentId(int parentid) {
		String sql = "select * from kb_category where parent_id = ? ";
		
		List<Category> list = null;
		
		try {
			list = queryRunner.query(sql, new BeanListHandler<Category>(Category.class),parentid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public List<Category> findByParent() {
		String sql = "select * from kb_category where parent_id = ? ";
		List<Category> list = null;
		try {
			list = queryRunner.query(sql, new BeanListHandler<Category>(Category.class),0);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int getTotalCount() {
		String sql="select count(1) from kb_category";
		try {
		return	queryRunner.query(sql, new ScalarHandler<Long>()).intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;

	}
/**
 * 根据父级id，获取它的二级类别，并按分页查询  默认展示五个

 */
	@Override
	public List<Product> getCategoryTop(int parentid) {
		String sql = "select * from kb_product where category_id in(select id from kb_category where id = ? or parent_id=? and status = 4 order by update_time) limit 0,5";
		try {
			return queryRunner.query(sql, new BeanListHandler<Product>(Product.class),parentid,parentid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
/**
 * 根据商品分类id 查询其下的所有商品  并且状态不为2 (下架) 
 */
	@Override
	public List<Product> getCategoruByCid(int cid, Page<Product> page) {
		
		String sql = "select * from kb_product p,(SELECT id FROM kb_category where id =? or parent_id = ?) c where c.id = p.category_id and p.status!=2 order by update_time desc LIMIT ?,?";
		
		try {
			return queryRunner.query(sql, new BeanListHandler<Product>(Product.class),cid,cid,page.getStartIndex(),page.getPageSize());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public int getCategoruByCidCount(int cid) {
		String sql = "select count(1) from kb_product p,(SELECT id FROM kb_category where id =? or parent_id = ?) c where c.id = p.category_id";
		try {
			return queryRunner.query(sql, new ScalarHandler<Long>(),cid,cid).intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	public List<CategoryDto> getCategoryDtos(int id){
		String sql = "select name,id as value from kb_category where parent_id=?";
		try {
			List<CategoryDto> query = queryRunner.query(sql,new BeanListHandler<CategoryDto>(CategoryDto.class),id);
			return query;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}

}
